package com.wpzf.zxhc.utils;

import java.sql.*;
import java.util.*;
import java.sql.PreparedStatement;

public class MdbFileOperation {
    /**
     * 读取.mdb文件
     *
     * @param mdbPath  mdb文件地址
     * @param username 用户名
     * @param password 密码
     */
    public List<Map<String, Object>> resolverMdb(String mdbPath, String username, String password) throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        Properties prop = new Properties();
        //设置编码
        prop.put("charSet", "UTF-8");
        prop.put("user", username);
        prop.put("password", password);
        //数据地址
        String dbUrl = "jdbc:ucanaccess://" + mdbPath;
        //引入驱动
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();
        Connection conn = null;
        ResultSet tables = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            //连接数据库资源
            conn = DriverManager.getConnection(dbUrl, prop);
            tables = conn.getMetaData().getTables(mdbPath, null, null, new String[]{"TABLE"});
            //遍历获取多张表数据
            while (tables.next()) {
                Map<String, Object> tableMap = new HashMap<>(16);
                Set<String> columnList = new HashSet<>();
                List<Map<String, String>> dataList = new ArrayList<>();
                String tableName = tables.getString(3);
                preparedStatement = conn.prepareStatement("select * from " + tableName);
                rs = preparedStatement.executeQuery();
                ResultSetMetaData data = rs.getMetaData();
                while (rs.next()) {
                    Map<String, String> map = new HashMap<>();
                    for (int i = 1; i <= data.getColumnCount(); i++) {
                        //列名
                        String columnName = data.getColumnName(i);
                        map.put(columnName, rs.getString(i));
                        columnList.add(columnName);
                    }
                    dataList.add(map);
                }
                tableMap.put("name", tableName);
                tableMap.put("column", columnList);
                tableMap.put("data", dataList);
                list.add(tableMap);
            }
        } catch (Exception ex) {
            //logger.error("resolverMdb is error!!!", e);
        } finally {
            closeAll(conn, preparedStatement, tables, rs);
        }
        return list;
    }

    /**
     * 关闭所有的资源链接
     *
     * @param conn              连接
     * @param preparedStatement 初始化 执行语句
     * @param tables            结果集
     * @param rs                结果集
     */
    private void closeAll(Connection conn, PreparedStatement preparedStatement, ResultSet tables, ResultSet rs) {
        try {
            if (null != rs) {
                rs.close();
            }
            if (null != tables) {
                tables.close();
            }
            if (null != preparedStatement) {
                preparedStatement.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (Exception ignore) {
        }
    }

}
